/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.eschool.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Time;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.eschool.model.Room;
import com.eschool.model.ScheduleSlot;
import com.eschool.model.Teacher;
import com.eschool.util.DateFormaterUtil;
import com.eschool.util.HibernateUtil;
import com.eschool.util.JdbcUtil;

/**
 *
 * @author Ammar
 */

public class ScheduleDao{

	public static boolean clearSchedule(int branchId) {
    	
    	boolean isCleared = false;
    	
    	Connection connection = JdbcUtil.openConnection();
		
		try{
			CallableStatement storedProc = connection.prepareCall("{ call spTimetableClear(?) }");
			
			storedProc.setInt(1, branchId);		

			storedProc.execute();
		
			isCleared = true;
			connection.close();
		}
		
		catch(Exception e){
			isCleared = false;
			connection.close();
			e.printStackTrace();
		}
		finally{
			return isCleared;
		}
	}
	
	public static ScheduleSlot getScheduleByProperty(String property, Object value) {
		
		Session session = HibernateUtil.currentSession();
	      
		Criteria crit = session.createCriteria(ScheduleSlot.class);
		crit.add(Restrictions.eq(property, value));
		
		ScheduleSlot s = (ScheduleSlot) crit.uniqueResult();

        HibernateUtil.closeSession(session);

        return s;
	}
	
	public static List getSchedules(Map<String , Object> conditions) {
	
		Session session = HibernateUtil.currentSession();
		Criteria crit = session.createCriteria(ScheduleSlot.class).addOrder(Order.asc("scheduleId"));
		
		if (conditions != null)
			
			for (Iterator iterator = conditions.entrySet().iterator(); iterator.hasNext();) {
			
				Map.Entry condition = (Map.Entry) iterator.next();
				
				crit.add(Restrictions.eq(((String) condition.getKey()), condition.getValue()));
			}
		
		List scheduleSlots = (List<ScheduleSlot>) crit.list();
		
        HibernateUtil.closeSession(session);
        
        if(scheduleSlots == null)
        	return Collections.EMPTY_LIST;

        return scheduleSlots;
	}

	public static boolean addTempSlot(ScheduleSlot scheduleSlot) {
    	
    	boolean isInserted = false;
    	
    	Connection connection = JdbcUtil.openConnection();
		
		try{
			CallableStatement storedProc = connection.prepareCall("{ call spTempScheduleInsert(?,?,?,?,?,?,?,?,?,?) }");
			
			storedProc.setInt(1, scheduleSlot.getBranch().getBranchId());
			storedProc.setInt(2, scheduleSlot.getSection().getSectionId());
			storedProc.setInt(3, scheduleSlot.getTeacher().getTeacherId());
			storedProc.setInt(4, scheduleSlot.getCourse().getCourseId());
			storedProc.setInt(5, scheduleSlot.getRoom().getRoomId());
			storedProc.setTime(6, scheduleSlot.getStartTime());
			storedProc.setTime(7, scheduleSlot.getEndTime());
			storedProc.setString(8, DateFormaterUtil.format(DateFormaterUtil.SQL_DATE,scheduleSlot.getValidFrom()));
			storedProc.setString(9, DateFormaterUtil.format(DateFormaterUtil.SQL_DATE,scheduleSlot.getValidTill()));
			storedProc.setInt(10, scheduleSlot.getDay());
			
			storedProc.execute();
		
			isInserted = true;
			connection.close();
		}
		
		catch(Exception e){
			isInserted = false;
			connection.close();
			e.printStackTrace();
		}
		finally{
			return isInserted;
		}
	}
	
	public static boolean isFree(Teacher teacher, Time classStartTime, Time classEndTime, int day  ) {
		
		Session session = HibernateUtil.currentSession();
		Criteria crit = session.createCriteria(ScheduleSlot.class);
		
		crit.add(Restrictions.eq("teacher", teacher));
		crit.add(Restrictions.eq("day", day))
		.add(Restrictions.sqlRestriction(( "(('"+new Time(classStartTime.getTime() + 1000) + "' BETWEEN StartTime AND EndTime) OR ('"+
				new Time(classEndTime.getTime()-1000 ) + "' BETWEEN StartTime AND EndTime));")));
					
		List scheduleSlots = (List<ScheduleSlot>) crit.list();
		
        HibernateUtil.closeSession(session);
        
        if(scheduleSlots.size()==0 || scheduleSlots==null)
        	return true;
        
        return false;
	}
	
	public static boolean isRoomFree(Room room, Time classStartTime, Time classEndTime, int day){
		
		Session session = HibernateUtil.currentSession();
		
		Criteria crit = session.createCriteria(ScheduleSlot.class);
		crit.add(Restrictions.eq("room", room));
		crit.add(Restrictions.eq("day", day))
					.add(Restrictions.sqlRestriction(( "(('"+new Time(classStartTime.getTime() + 1000) + 
							"' BETWEEN StartTime AND EndTime) OR ('" + new Time(classEndTime.getTime()-1000 ) + 
								"' BETWEEN StartTime AND EndTime));")));
		
		List busySlots = crit.list();
		
		HibernateUtil.closeSession(session);
		
		if(busySlots.size()==0 || busySlots == null)
        	return true;
        
        return false;
		
	}
	
	public static List roomBusyList(List<Room> rooms, Time classStartTime, Time classEndTime, int day){
		
		Session session = HibernateUtil.currentSession();
		
		Criteria crit = session.createCriteria(ScheduleSlot.class);
		
		crit.add(Restrictions.eq("day", day))
					.add(Restrictions.sqlRestriction(( "(('"+new Time(classStartTime.getTime() + 1000) + 
							"' BETWEEN StartTime AND EndTime) OR ('" + new Time(classEndTime.getTime()-1000 ) + 
								"' BETWEEN StartTime AND EndTime))")));
		String roomsRestrioctions = null ;
		
		for(int i=0; i<rooms.size(); i++){
			
			if(rooms.size() == 1){
				roomsRestrioctions = " this_.RoomID=" + ((Room) (rooms.get(0)) ).getRoomId() + ";";
				break;
			}
			
			else if( i==0 )
				roomsRestrioctions = " ( ";
			
			else if( i>0 && i<rooms.size()  )
				roomsRestrioctions += " OR ";
			
			if ( i<rooms.size()-1  )
				roomsRestrioctions += " this_.RoomID=" + ((Room) (rooms.get(i)) ).getRoomId();
			else if(( i == rooms.size()-1  )){
				roomsRestrioctions += " this_.RoomID=" + ((Room) (rooms.get(i)) ).getRoomId() + " );";
				break;
			}
		}
		
		crit.add(Restrictions.sqlRestriction(roomsRestrioctions));
		
		List busySlots = crit.list();
		
		HibernateUtil.closeSession(session);
		
		if(busySlots.size()==0 || busySlots==null)
			return busySlots;
        
        return busySlots;
		
	}

	
	
	public static List teacehersBusyList(List<Teacher> teachers, Time classStartTime, Time classEndTime, int day){
		
		Session session = HibernateUtil.currentSession();
		
		Criteria crit = session.createCriteria(ScheduleSlot.class);
		
		crit.add(Restrictions.eq("day", day))
					.add(Restrictions.sqlRestriction(( "(('"+new Time(classStartTime.getTime() + 1000) + 
							"' BETWEEN StartTime AND EndTime) OR ('" + new Time(classEndTime.getTime()-1000 ) + 
								"' BETWEEN StartTime AND EndTime))")));
		String teachersRestrioctions = null ;
		
		for(int i=0; i<teachers.size(); i++){
			
			if(teachers.size() == 1){
				teachersRestrioctions = " this_.TeacherID=" + ((Teacher) (teachers.get(0)) ).getTeacherId() + ";";
				break;
			}
			
			else if( i==0 )
				teachersRestrioctions = " ( ";
			
			else if( i>0 && i<teachers.size()  )
				teachersRestrioctions += " OR ";
			
			if ( i<teachers.size()-1  )
				teachersRestrioctions += " this_.TeacherID=" + ((Teacher) (teachers.get(i)) ).getTeacherId();
			else if(( i == teachers.size()-1  )){
				teachersRestrioctions += " this_.TeacherID=" + ((Teacher) (teachers.get(i)) ).getTeacherId() + " );";
				break;
			}
		}
		
		crit.add(Restrictions.sqlRestriction(teachersRestrioctions));
		
		List busySlots = crit.list();
		
		HibernateUtil.closeSession(session);
		
		if(busySlots.size()==0)
        	return null;
        
        return busySlots;
	}
}
